-- MySQL Script generated by MySQL Workbench
-- Sun Jul 14 09:25:39 2019
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema ac_advert
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `advert`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `advert` (
  `adv_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `msg_type` INT NOT NULL,
  `msg_text` VARCHAR(512) NOT NULL,
  `date_from` DATETIME NULL DEFAULT NULL,
  `date_to` DATETIME NULL DEFAULT NULL,
  `hours` VARCHAR(64) NULL DEFAULT '0-24;',
  `is_vip` TINYINT NULL DEFAULT 0,
  `admin_flags` VARCHAR(64) NULL DEFAULT NULL,
  `views` INT NULL DEFAULT -1,
  `day_of_week` VARCHAR(64) NULL DEFAULT '1-7;',
  `show` TINYINT NULL DEFAULT 1,
  `order` INT NULL DEFAULT 1000,
  PRIMARY KEY (`adv_id`),
  UNIQUE INDEX `adv_id_UNIQUE` (`adv_id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `hud_style`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `hud_style` (
  `adv_id` INT UNSIGNED NOT NULL,
  `color1` VARCHAR(24) NULL DEFAULT '255 255 255 255',
  `color2` VARCHAR(24) NULL DEFAULT '255 255 255 255',
  `effect` INT NULL DEFAULT 1,
  `fadein` FLOAT NULL DEFAULT 0.1,
  `fadeout` FLOAT NULL DEFAULT 0.1,
  `holdtime` FLOAT NULL DEFAULT 10,
  `x` FLOAT NULL DEFAULT 0.5,
  `y` FLOAT NULL DEFAULT 0.5,
  `fxtime` FLOAT NULL DEFAULT 0.1,
  PRIMARY KEY (`adv_id`),
  UNIQUE INDEX `style_id_UNIQUE` (`adv_id` ASC),
  CONSTRAINT `hud_style_adv_id`
    FOREIGN KEY (`adv_id`)
    REFERENCES `advert` (`adv_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `magic_words`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `magic_words` (
  `word_id` INT NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(64) NOT NULL,
  `value` VARCHAR(256) NOT NULL,
  PRIMARY KEY (`word_id`),
  UNIQUE INDEX `word_id_UNIQUE` (`word_id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `server_ads`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `server_ads` (
  `srv_id` INT UNSIGNED NOT NULL,
  `adv_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`srv_id`, `adv_id`),
  INDEX `server_ads_adv_id_idx` (`adv_id` ASC),
  CONSTRAINT `server_ads_srv_id`
    FOREIGN KEY (`srv_id`)
    REFERENCES `servers` (`srv_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `server_ads_adv_id`
    FOREIGN KEY (`adv_id`)
    REFERENCES `advert` (`adv_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `servers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `servers` (
  `srv_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip` VARCHAR(64) NOT NULL,
  `port` INT NOT NULL,
  `title` VARCHAR(128) NULL DEFAULT 'Server',
  `rcon` VARCHAR(192) NULL,
  `adv_time` FLOAT NULL DEFAULT 45,
  PRIMARY KEY (`srv_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- View `ads`
-- -----------------------------------------------------
create  OR REPLACE view ads as
SELECT s.srv_id, a.*, h.color1, h.color2, h.effect, h.fadein, h.fadeout, h.holdtime, h.x, h.y, h.fxtime FROM server_ads as s join advert as a using(adv_id) left join hud_style as h using(adv_id) where a.show = 1 order by a.`order`;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
